import pymssql
import xml.etree.ElementTree as ET
import config


def xmlToDB(file):
    # 解析 XML 文件
    tree = ET.parse(f'D:\\文档\\DLMU\\ITC2019\\{file}.xml')
    root = tree.getroot()

    # SQL Server 数据库连接字符串
    server = config.url
    user = config.user
    password = config.password
    database = config.database
    tablepreffix_name = file.replace("-","_")

    try:
        # 打开数据库连接（ip/数据库用户名/登录密码/数据库名）
        db = pymssql.connect(server=server, user=user, password=password, database=database)
        print("数据库连接成功")
        # 使用 cursor() 方法创建一个游标对象 cursor
        cursor = db.cursor()
    except pymssql.Error as e:
        print("数据库连接失败：" + str(e))

    # 处理 选课表 数据
    room_list = []
    room_dist_list = []
    room_unavailable_list = []
    for room in root.findall('rooms/room'):
        # Insert into tbl_rooms
        room_id = room.get('id')  # Assuming the XML has an 'id' attribute for the room
        capacity = room.get('capacity')  # If capacity is provided
        room_list.append((room_id, capacity))
        # cursor.execute("INSERT INTO agh-fal17_Room (id, capacity) VALUES (%s, %s)", (room_id, capacity))

        # Insert into tbl_room_availability
        for unavailable in room.findall('unavailable'):
            days = unavailable.get('days')
            start = unavailable.get('start')
            length = unavailable.get('length')
            weeks = unavailable.get('weeks')
            room_unavailable_list.append((room_id, days, start, length, weeks))
            # cursor.execute(
            #     "INSERT INTO agh-fal17_Room_UnAvailability (room_id, days, start, length, weeks) VALUES (%s, %s, %s, %s, %s)",
            #     (room_id, days, start, length, weeks))

        # Insert into tbl_room_travel_times
        for travel in room.findall('travel'):
            to_room_id = travel.get('room')
            value = travel.get('value')
            room_dist_list.append((room_id, to_room_id, value))
            # cursor.execute("INSERT INTO agh-fal17_Room_Travel_Times (room_id, to_room_id, value) VALUES (%s, %s, %s)",
            #                (room_id, to_room_id, value))

    # 处理 选课表 数据
    s_c_list = []
    for student in root.findall('students/student'):
        id = student.get('id')

        # 遍历课程信息
        for travel_element in student.findall('course'):
            c_id = travel_element.get('id')
            s_c_list.append((id, c_id))
            # 插入选课信息
            # cursor.execute("INSERT INTO agh-fal17_StudentCourse(Sno, c_id) VALUES (%s, %s)",
            #           (id, c_id))

    # 处理课程信息表数据
    task_list = []
    class_room_penalty_list = []
    class_time_penalty_list = []
    for course in root.findall('courses/course'):
        id = course.get('id')

        for task in course.findall('config/subpart/class'):
            class_id = task.get('id')
            limit = task.get('limit')
            isRoom = True if task.get('room') is None else False
            task_list.append((id, class_id, limit, isRoom))
            for room in task.findall('room'):
                room_id = room.get('id')
                r_penalty = room.get('penalty')
                class_room_penalty_list.append((class_id, room_id, r_penalty))
            for time in task.findall('time'):
                days = time.get('days')
                start = time.get('start')
                length = time.get('length')
                weeks = time.get('weeks')
                t_penalty = time.get('penalty')
                class_time_penalty_list.append((class_id, days, start, length, weeks, t_penalty))

    # 约束惩罚列表
    distribution_list = []
    required_list = []
    for distribution in root.findall('distributions/distribution'):
        type = distribution.get('type')
        penalty = distribution.get('penalty')
        cid_list = []
        if penalty is None:
            required = distribution.get('required')
            if required:
                for classes in distribution.findall('class'):
                    cid = classes.get('id')
                    cid_list.append(cid)
                cid_list_str = ','.join(cid_list)
                required_list.append((type, cid_list_str))
            continue
        for classes in distribution.findall('class'):
            cid = classes.get('id')
            cid_list.append(cid)
        cid_list_str = ','.join(cid_list)
        distribution_list.append((type, cid_list_str, penalty))

    # print(required_list)

    try:
        # 教室相关表
        room_sql = f"INSERT INTO {tablepreffix_name}_Room (id, capacity) VALUES (%s, %s)"
        room_dist_sql = f"INSERT INTO {tablepreffix_name}_Room_Travel_Times(room_id, to_room_id, value) VALUES (%s, %s, %s)"
        room_unavailable_sql = f"INSERT INTO {tablepreffix_name}_Room_UnAvailability(room_id, days, start, length, weeks) VALUES (%s, %s, %s, %s, %s)"

        # 学生选课相关表
        s_c_sql = f"INSERT INTO {tablepreffix_name}_StudentCourse(Sno, c_id) VALUES (%s, %s)"

        # 教学任务相关表
        task_sql = f"INSERT INTO {tablepreffix_name}_ScoreManage(cid, class_id, limit, is_room) VALUES (%s, %s, %s, %s)"
        class_room_penalty_sql = f"INSERT INTO {tablepreffix_name}_ScoreManageRPenalty(class_id, rid, penalty) VALUES (%s, %s, %s)"
        class_time_penalty_sql = f"INSERT INTO {tablepreffix_name}_ScoreManageTPenalty(class_id, days, start, length, weeks, penalty) VALUES (%s, %s, %s, %s, %s, %s)"
        distribution_penalty_sql = f"INSERT INTO {tablepreffix_name}_DistributionPenalty(type, classids, penalty) VALUES (%s, %s, %s)"
        required_sql = f"INSERT INTO {tablepreffix_name}_DistributionRequired(type, classids) VALUES (%s, %s)"

        # 批量插入
        cursor.executemany(room_sql, room_list)
        cursor.executemany(room_dist_sql, room_dist_list)
        cursor.executemany(room_unavailable_sql, room_unavailable_list)
        cursor.executemany(s_c_sql, s_c_list)
        cursor.executemany(task_sql, task_list)
        cursor.executemany(class_room_penalty_sql, class_room_penalty_list)
        cursor.executemany(class_time_penalty_sql, class_time_penalty_list)
        cursor.executemany(distribution_penalty_sql, distribution_list)
        cursor.executemany(required_sql, required_list)

        # 提交事务
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        # 关闭数据库连接
        cursor.close()

 # 全部导入数据
if __name__ == '__main__':
    for file in config.file:
        xmlToDB(file)
        print("导入成功")

